"""
Step by Step

逐步的生成SQL
"""

from nl2sql.engines.decompose_query import DecomposeQuery
from nl2sql.engines.sql_generator import SQLGenerator
from nl2sql.datasource.excel_datasource import ExcelDatasource
from typing import List

excel_path = "D:/PythonProject_airline/nl2sql_aline/examples/航空公司航班信息表.xlsx"

excel_ds = ExcelDatasource(source_path=excel_path, auto_create_sqlite=True)

query = '查一下平均油耗大于20升的航空公司里面且是中国的航司，他们的平均载客量与最大载货量之比最小的是哪家航司？并告诉我它们所有的航线'

# 实现路线：不拆解问题，直接硬上！
# sg = SQLGenerator(datasource=excel_ds)
# sql = sg.generate(context=query)

# try:
#     result = excel_ds.execute_sql(sql=sql)
# except Exception as error:
#     print(error)


# 实现路线2: 拆解问题，分而治之
dq = DecomposeQuery()
decomposed_query: str = dq.decompose_query(query=query)
decomposed_query: List[str] = eval(decomposed_query)


# 接下来，就分步骤来编写SQL
sg = SQLGenerator(datasource=excel_ds)

record = ""   # 用来记录中间生成的临时上下文

for sub_query in decomposed_query:
    record = record + sub_query + '\n'
    sql = sg.generate(context=record)
    record = record + '\n' + f'sql: {sql}' + '\n'


